package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.fact.dto.MakeDTO;
import com.fact.dto.MarketDTO;
import com.fact.dto.ResponsibilityDTO;
import com.fact.dto.UserDTO;
import com.fact.utils.connection.ConnectionManager;


public class ResponsibilityService {
	
	public ArrayList<UserDTO> getResponsiblityList() 
	{
		Connection conn = ConnectionManager.getConnection();
		   String sql ="Select res.MAKE_ID,res.MARKET_ID,makes.MAKE_NAME,market.MARKET_NAME,res.USER_ID,muser.USERNAME,res.ROLE_ID,rol.ROLE_NAME  " +
			"from RESPONSIBILITIES res,MST_MAKES makes,MST_MARKETS market,MST_USER muser,MST_ROLE rol " +
			"Where res.MAKE_ID=makes.MAKE_ID " +
			"And   res.MARKET_ID=market.MARKET_ID " +
			"And   res.USER_ID=muser.USERID " +
			"And   res.ROLE_ID=rol.ROLE_ID " +
			"Order by userid,make_id,market_id ";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
		   
		   ArrayList<UserDTO> userList=new ArrayList<UserDTO>();
		   ArrayList<ResponsibilityDTO> responsiblityList=new ArrayList<ResponsibilityDTO>();
	
 		   String preUserId="";
		   String preRoleId="";
		   int counter=0;
		   boolean flag=false;
		   UserDTO user=new UserDTO();
		   ResponsibilityDTO responsibility=new ResponsibilityDTO();
		   try
			{
				stmt = conn.prepareStatement(sql);
			    
				r = stmt.executeQuery();
				while (r.next())
				{

					if(!preRoleId.equalsIgnoreCase(r.getString("ROLE_ID")) || !preUserId.equalsIgnoreCase(r.getString("USER_ID")))
					{
						if(counter!=0)
						{
							userList.add(user);
							user=new UserDTO();
							responsibility=new ResponsibilityDTO();
							
							user.setUserTypeId(r.getInt("ROLE_ID"));
							user.setUserId(r.getString("USER_ID"));							
							user.setUserTypeName(r.getString("ROLE_NAME"));
							user.setUserName(r.getString("USERNAME"));
							
							responsibility.setMakeId(r.getInt("MAKE_ID"));
							responsibility.setMarketId(r.getInt("MARKET_ID"));
							responsibility.setMakeName(r.getString("MAKE_NAME"));
							responsibility.setMarketName(r.getString("MARKET_NAME"));
							
							if(user.getResponsibilityList()==null)
								user.setResponsibilityList(new ArrayList<ResponsibilityDTO>());
							
							user.getResponsibilityList().add(responsibility);
							userList.add(user);
							flag=false;
							
						}
						else
						{
							user=new UserDTO();
							responsibility=new ResponsibilityDTO();
							
							user.setUserTypeId(r.getInt("ROLE_ID"));
							user.setUserId(r.getString("USER_ID"));
							user.setUserTypeName(r.getString("ROLE_NAME"));
							user.setUserName(r.getString("USERNAME"));
							
							responsibility.setMakeId(r.getInt("MAKE_ID"));
							responsibility.setMarketId(r.getInt("MARKET_ID"));
							responsibility.setMakeName(r.getString("MAKE_NAME"));
							responsibility.setMarketName(r.getString("MARKET_NAME"));
							
							if(user.getResponsibilityList()==null)
								user.setResponsibilityList(new ArrayList<ResponsibilityDTO>());
							
							user.getResponsibilityList().add(responsibility);
							

							
						}
					}
					else
					{
						responsibility=new ResponsibilityDTO();
						responsibility.setMakeId(r.getInt("MAKE_ID"));
						responsibility.setMarketId(r.getInt("MARKET_ID"));
						responsibility.setMakeName(r.getString("MAKE_NAME"));
						responsibility.setMarketName(r.getString("MARKET_NAME"));
						
						if(user.getResponsibilityList()==null)
							user.setResponsibilityList(new ArrayList<ResponsibilityDTO>());
						
						user.getResponsibilityList().add(responsibility);
						flag=true;
					}
					preRoleId=r.getString("ROLE_ID");
					preUserId=r.getString("USER_ID");
					
					counter++;
					
				}
			} //End of Try
	
	catch (Exception e){e.printStackTrace();}
		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
		{e.printStackTrace();}stmt = null;conn = null;}

		return userList;
	}
	
	
	public boolean updateResponsibility(UserDTO user,List<String> assignedMakeList,List<String> assignedMarketList) 
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete RESPONSIBILITIES Where USER_ID=?";
		boolean response=false;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,user.getUserId());
				stmt.executeUpdate();
				
				for(int i=0;i<assignedMakeList.size();i++)
				{
					for(int j=0;j<assignedMarketList.size();j++)
					{
						sql=" Insert into RESPONSIBILITIES(MAKE_ID,MARKET_ID,ROLE_ID,USER_ID) VALUES(?,?,?,?)";
						
						stmt = conn.prepareStatement(sql);
						stmt.setInt(1,Integer.valueOf(assignedMakeList.get(i)));
						stmt.setInt(2,Integer.valueOf(assignedMarketList.get(j)));
						stmt.setInt(3,user.getUserTypeId());
						stmt.setString(4,user.getUserId());
						
						stmt.executeUpdate();
						
					}
				}
				
				response=true;
			} 
			catch (Exception e){e.printStackTrace();}
	 		
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
			{e.printStackTrace();}stmt = null;conn = null;}

		
		return response;
		
	}
	

}
